package com.cscec5b.inspection.repository;

import com.cscec5b.inspection.dto.TaskListItem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * TaskRepository
 * 职责：
 * 1）任务列表的查询/计数（按用户、状态、关键字、项目过滤）
 * 2）任务头部信息查询（项目名、部位、类型）
 * 3）动态表单所需：列探测（information_schema）、标准表/数据表读取、数据表 UPSERT
 */
@Repository
public class TaskRepository {

    private final JdbcTemplate jdbc;
    private static final Logger log = LoggerFactory.getLogger(TaskRepository.class);

    // NEW: 可切换的多对多表名；
    private static final String USER_PROJECT_TABLE = "`user_project`";
    private static final String INSPECTION_DATA_TABLE = "`detail_inspection_original`";

    public TaskRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    // NEW: 任务列表查询（真正按状态过滤，并且后端生成中文任务名）
    public List<TaskListItem> findTasks(long userId, String stateVal, Long projectId, String keyword,
                                        int offset, int size, boolean todoOrderAsc) {

//        String base =
//                " FROM tasks t " +
//                        " JOIN projects p ON p.project_id = t.project_id " +
//                        " JOIN " + USER_PROJECT_TABLE + " up ON up.project_id = t.project_id " +
//                        " WHERE up.user_id = ? AND t.task_state = ? ";  // CHANGED: 这里按状态过滤
        String base =
                " FROM tasks t " +
                        " JOIN projects p ON p.project_id = t.project_id " +
                        " JOIN " + USER_PROJECT_TABLE + " up ON up.project_id = t.project_id " +
                        " JOIN " + INSPECTION_DATA_TABLE + " dio ON dio.task_id = t.task_id " +
                        " WHERE up.user_id = ? AND t.task_state = ? " +
                        "   AND dio.members IS NOT NULL " +
                        "   AND JSON_VALID(dio.members) " +
                        "   AND JSON_CONTAINS(CAST(dio.members AS JSON), " +
                        "                      JSON_QUOTE( (SELECT u.user_code FROM users u WHERE u.id = ?) )) ";



        List<Object> params = new ArrayList<>(Arrays.asList(userId, stateVal));

        params.add(userId); // ★ 第3个占位符：子查询 u.id = ?
        if (projectId != null) {
            base += " AND t.project_id = ? ";
            params.add(projectId);
        }
        if (keyword != null && !keyword.isBlank()) {
            base += " AND (t.task_location LIKE ? OR p.project_name LIKE ?) ";
            String kw = "%" + keyword.trim() + "%";
            params.add(kw); params.add(kw);
        }

        String order = todoOrderAsc ? "ASC" : "DESC";

        String sql =
                // ★ DISTINCT 防止同一任务因多条 dio 记录被重复
                "SELECT DISTINCT " +
                        "  t.task_id AS taskId, t.project_id AS projectId, p.project_name AS projectName, " +
                        "  t.task_type AS taskType, t.task_location AS taskLocation, t.task_state AS taskState, " +
                        "  CASE t.task_state WHEN '0' THEN '待办' WHEN '1' THEN '已办' END AS taskStateCn, " +
                        "  CONCAT(t.task_location, '-', " +
                        "    CASE t.task_type " +
                        "      WHEN 'rebar_material'        THEN '钢筋原材料检验批' " +
                        "      WHEN 'rebar_process'         THEN '钢筋加工检验批' " +
                        "      WHEN 'rebar_installation'    THEN '钢筋安装检验批' " +
                        "      WHEN 'formwork_installation' THEN '模板安装检验批' " +
                        "      WHEN 'concrete_mix'          THEN '混凝土拌合物检验批' " +
                        "      WHEN 'concrete_construction' THEN '混凝土施工记录' " +
                        "      WHEN 'cast_in_situ_structure'THEN '现浇结构实体检验记录' " +
                        "    END" +
                        "  ) AS taskName, " +
                        "  t.task_starttime AS taskStarttime, t.task_deadline AS taskDeadline " +
                        base +
                        " ORDER BY t.task_deadline " + order +
                        " LIMIT ? OFFSET ?";
        // NEW: 在 SELECT 中拼出中文任务名（避免前端或视图依赖）
//        String sql =
//                "SELECT " +
//                        "  t.task_id AS taskId, t.project_id AS projectId, p.project_name AS projectName, " +
//                        "  t.task_type AS taskType, t.task_location AS taskLocation, t.task_state AS taskState, " +
//                        "  CASE t.task_state WHEN '0' THEN '待办' WHEN '1' THEN '已办' END AS taskStateCn, " +
//                        "  CONCAT(t.task_location, '-', " +
//                        "    CASE t.task_type " +
//                        "      WHEN 'rebar_material'        THEN '钢筋原材料检验批' " +
//                        "      WHEN 'rebar_process'         THEN '钢筋加工检验批' " +
//                        "      WHEN 'rebar_installation'    THEN '钢筋安装检验批' " +
//                        "      WHEN 'formwork_installation' THEN '模板安装检验批' " +
//                        "      WHEN 'concrete_mix'          THEN '混凝土拌合物检验批' " +
//                        "      WHEN 'concrete_construction' THEN '混凝土施工记录' " +   // CHANGED: 修正为中文，而不是英文 taskType
//                        "      WHEN 'cast_in_situ_structure'THEN '现浇结构实体检验记录' " +
//                        "    END" +
//                        "  ) AS taskName, " +
//                        "  t.task_starttime AS taskStarttime, t.task_deadline AS taskDeadline " +
//                        base +
//                        " ORDER BY t.task_deadline " + order +
//                        " LIMIT ? OFFSET ?";



        params.add(size);
        params.add(offset);

//        return jdbc.query(sql, params.toArray(),
//                BeanPropertyRowMapper.newInstance(TaskListItem.class));
        try {
            return jdbc.query(sql, params.toArray(),
                    BeanPropertyRowMapper.newInstance(TaskListItem.class));
        } catch (org.springframework.dao.DataAccessException e) {
            log.error("SQL error\n{}\nparams={}", sql, params, e.getMostSpecificCause());
            throw e;
        }

    }

    // NEW: 计数（与列表同条件）
    public long countTasks(long userId, String stateVal, Long projectId, String keyword) {
//        String base =
//                " FROM tasks t " +
//                        " JOIN " + USER_PROJECT_TABLE + " up ON up.project_id = t.project_id " +
//                        " WHERE up.user_id = ? AND t.task_state = ? ";  // CHANGED: 这里按状态过滤
        String base =
                " FROM tasks t " +
                        " JOIN projects p ON p.project_id = t.project_id " +
                        " JOIN " + USER_PROJECT_TABLE + " up ON up.project_id = t.project_id " +
                        " JOIN " + INSPECTION_DATA_TABLE + " dio ON dio.task_id = t.task_id " +
                        " WHERE up.user_id = ? AND t.task_state = ? " +
                        "   AND dio.members IS NOT NULL " +
                        "   AND JSON_VALID(dio.members) " +
                        "   AND JSON_CONTAINS(CAST(dio.members AS JSON), " +
                        "                      JSON_QUOTE( (SELECT u.user_code FROM users u WHERE u.id = ?) )) ";

        String sql = "SELECT COUNT(DISTINCT t.task_id) " + base;


        List<Object> params = new ArrayList<>(Arrays.asList(userId, stateVal));
        params.add(userId); // ★ 第3个占位符：子查询 u.id = ?

        if (projectId != null) {
            base += " AND t.project_id = ? ";
            params.add(projectId);
        }
        if (keyword != null && !keyword.isBlank()) {
            base += " AND (t.task_location LIKE ? ) ";  // 计数可简化一点
            params.add("%" + keyword.trim() + "%");
        }

//        Long total = jdbc.queryForObject("SELECT COUNT(1) " + base, params.toArray(), Long.class);
        Long total = jdbc.queryForObject("SELECT COUNT(DISTINCT t.task_id) " + base, params.toArray(), Long.class);

        return total == null ? 0 : total;
    }

    // ========= 2) 头部信息 =========

    /** 任务与项目基本信息（项目名、任务类型、部位） */
    // ===== 头部信息 =====
    // CHANGED: 同时把 t.inspect_items 查出来
    public Map<String,Object> findTaskHeader(long taskId){
        String sql = """
        SELECT
            t.task_id,
            t.project_id,
            t.task_type,
            t.task_location,
            t.fail_resample,                                -- ★ NEW
            CASE WHEN JSON_VALID(t.inspect_items) THEN CAST(t.inspect_items AS CHAR)
                 ELSE t.inspect_items END AS inspect_items,
            p.project_name
        FROM tasks t
        JOIN projects p ON p.project_id = t.project_id
        WHERE t.task_id = ?
    """;
        return jdbc.queryForMap(sql, taskId);
    }



    // ========= 3) 动态表单（标准表 & 数据表） =========

    // TaskRepository.java 新增代码片段
// === 在类中新增一个内部 DTO，表示一行映射 ===
    public static class TypeItem {
        public String itemEn;
        public String itemZh;
        public int sortOrder;
        public boolean hasPhoto;
        public boolean hasPdf;

        public Integer itemType;
    }

    // === 在类中新增查询方法 ===
    public List<TypeItem> listTypeItems(String taskType) {
        String sql = """
        SELECT item_en, item_zh, sort_order, has_photo, has_pdf, item_type
        FROM task_type_item
        WHERE task_type = ?
        ORDER BY sort_order, id
        """;
        return jdbc.query(sql, (rs, i) -> {
            TypeItem ti = new TypeItem();
            ti.itemEn   = rs.getString("item_en");
            ti.itemZh   = rs.getString("item_zh");
            ti.sortOrder= rs.getInt("sort_order");
            ti.hasPhoto = rs.getBoolean("has_photo");
            ti.hasPdf   = rs.getBoolean("has_pdf");
//            ti.itemType = (Integer) rs.getObject("item_type");
            Object it = rs.getObject("item_type");
            ti.itemType  = (it == null ? null : ((Number) it).intValue());
            return ti;
        }, taskType);
    }


    /** information_schema 取列名 */
    public List<String> listColumns(String schema, String table) {
        String sql = """
            SELECT COLUMN_NAME
            FROM information_schema.columns
            WHERE table_schema=? AND table_name=?
            """;
        return jdbc.queryForList(sql, String.class, schema, table);
    }

    /** 读取标准表的一行（一般是模板数据，取第一行；如你有 task_id 维度，可改 where） */
    public Map<String, Object> loadStandardRow(String standardTable) {
        List<Map<String,Object>> list = jdbc.queryForList("SELECT * FROM " + standardTable + " LIMIT 1");
        return list.isEmpty()? Map.of() : list.get(0);
    }

    /** 读取数据表（按 task_id） */
    public Map<String, Object> loadDataRow(String dataTable, long taskId) {
        List<Map<String,Object>> list = jdbc.queryForList("SELECT * FROM " + dataTable + " WHERE task_id = ?", taskId);
        return list.isEmpty()? Map.of() : list.get(0);
    }

    /** 某表是否存在该列 */
    public boolean columnExists(String schema, String table, String column) {
        String sql = """
            SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema=? AND table_name=? AND column_name=?
            """;
        Integer n = jdbc.queryForObject(sql, Integer.class, schema, table, column);
        return n != null && n > 0;
    }

    // 按用户筛选inspect_item
    public List<String> listUserAllowedInspectionItems(long taskId, long userId) {
//        String sql = """
//        SELECT DISTINCT dio.inspection_item
//        FROM detail_inspection_original dio
//        WHERE dio.task_id = ?
//          AND dio.members IS NOT NULL
//          AND JSON_VALID(dio.members)
//          AND JSON_CONTAINS(CAST(dio.members AS JSON),
//                            JSON_QUOTE((SELECT u.user_code FROM users u WHERE u.id = ?)))
//        ORDER BY dio.task_item_id
//        """;   ---报错500
        String sql = """
        SELECT dio.inspection_item
        FROM detail_inspection_original dio
        WHERE dio.task_id = ?
            AND dio.members IS NOT NULL
            AND CAST(dio.members AS CHAR) LIKE CONCAT('%\"',
                (SELECT u.user_code FROM users u WHERE u.id = ?),
                '\"%')
        GROUP BY dio.inspection_item
        ORDER BY MIN(dio.task_item_id)
        """;
        return jdbc.query(sql, (rs, i) -> rs.getString(1), taskId, userId);
    }

    /** UPSERT：数据表中写入/更新（仅写入存在的列） */
    public void upsertDataRow(String dataTable, long taskId, Map<String, Object> values) {
        Integer exists = jdbc.queryForObject("SELECT COUNT(1) FROM " + dataTable + " WHERE task_id=?", Integer.class, taskId);
        boolean update = exists != null && exists > 0;

        if (!update) {
            // INSERT
            List<String> cols = new ArrayList<>();
            List<String> qs = new ArrayList<>();
            List<Object> ps = new ArrayList<>();
            cols.add("task_id"); qs.add("?"); ps.add(taskId);
            for (var e : values.entrySet()) { cols.add(e.getKey()); qs.add("?"); ps.add(e.getValue()); }
            String sql = "INSERT INTO " + dataTable + " (" + String.join(",", cols) + ") VALUES (" + String.join(",", qs) + ")";
            jdbc.update(sql, ps.toArray());
        } else {
            // UPDATE
            if (values.isEmpty()) return;
            StringBuilder set = new StringBuilder();
            List<Object> ps = new ArrayList<>();
            for (var e : values.entrySet()) { set.append(e.getKey()).append("=?,"); ps.add(e.getValue()); }
            set.setLength(set.length()-1);
            String sql = "UPDATE " + dataTable + " SET " + set + " WHERE task_id=?";
            ps.add(taskId);
            jdbc.update(sql, ps.toArray());
        }
    }

    // TaskRepository.java 追加

    /** 读取一个任务在 detail_inspection_original 的所有条目，按 inspection_item（中文名）分组 */
    public Map<String, Map<String, Object>> loadDetailOriginalByTask(long taskId) {
        String sql = """
        SELECT inspection_item, task_type, inspect_part, check_amount, passed_amount,
               is_passed, check_record, device_records, collector     -- ★ NEW
        FROM detail_inspection_original
        WHERE task_id = ?
    """;
        List<Map<String, Object>> rows = jdbc.queryForList(sql, taskId);
        Map<String, Map<String, Object>> map = new LinkedHashMap<>();
        for (Map<String,Object> r : rows) {
            map.put(String.valueOf(r.get("inspection_item")), r);
        }
        return map;
    }

    /** upsert 一条 detail_inspection_original（以 task_id + inspection_item 唯一约束为准） */
    // TaskRepository.java
    public void upsertDetailOriginal(long taskId, String taskType, String inspectionItemZh,
                                     String inspectPart, Integer checkAmount, Integer passedAmount,
                                     Integer isPassed, String checkRecord, String deviceRecords,
                                     String collector) {

        String sql = """
        INSERT INTO detail_inspection_original
          (task_id, task_type, inspection_item, inspect_part, check_amount, passed_amount,
           is_passed, check_record, device_records, collector)                       -- ★ NEW
        VALUES (?,?,?,?,?,?,?,?,?,?)
        ON DUPLICATE KEY UPDATE
           task_type      = VALUES(task_type),
           inspect_part   = VALUES(inspect_part),
           check_amount   = VALUES(check_amount),
           passed_amount  = VALUES(passed_amount),
           is_passed      = VALUES(is_passed),
           check_record   = VALUES(check_record),
           device_records = VALUES(device_records),
           collector      = VALUES(collector)                                        -- ★ NEW
    """;
        jdbc.update(sql, taskId, taskType, inspectionItemZh, inspectPart, checkAmount, passedAmount,
                isPassed, checkRecord, deviceRecords, collector);
    }





    // ========= 公共小工具 =========
    public static Integer asInt(Map<String,Object> row, String col){
        Object v = row.get(col);
        if (v == null) return null;
        if (v instanceof Number n) return n.intValue();
        try { return Integer.parseInt(String.valueOf(v)); } catch (Exception ignore) { return null; }
    }
    public static String asStr(Map<String,Object> row, String col){
        Object v = row.get(col);
        return v == null ? null : String.valueOf(v);
    }

    // 解析列名 "rebar_appearance_require" → prefix=rebar_appearance / suffix=require
    public record Parsed(String prefix, String suffix){}
    private static final Pattern P = Pattern.compile("^([a-z0-9_]+)_(require|sample_total|min_number|number|record|result|photo|pdf)$");
    public static Parsed parse(String col){
        Matcher m = P.matcher(col);
        if (m.matches()) return new Parsed(m.group(1), m.group(2));
        return null;
    }

}